"""
#!/usr/bin/python3
# coding:utf-8            
#
# Copyright (C) 2024 - 2024 Jasonakeke， Inc. All Rights Reserved 
# @Desc	   ：
# @Time    : 2024/8/16 22:06
# @Author  : Code_By_Jasonakeke
# @Email   : 2284037977@qq.com
# @File    : 09-ORM的高级操作2.py
# @IDE     : PyCharm
"""
from datetime import date

from sqlalchemy import create_engine, Column, Integer, String, DECIMAL, DATE, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship, backref
from sqlalchemy.sql.functions import count

# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

# 创建数据库引擎
engine = create_engine(DB_URI)

# 第一步：创建一个基类（所有 ORM 中的 O，对象模型的超级父类）
Base = declarative_base()


# 部门和员工之间，是一种典型的一（主）对多（从）的关系
class Dept(Base):
	__tablename__ = 't_dept'
	dept_no = Column(Integer, primary_key = True)
	d_name = Column(String(255))
	city = Column(String(50))

	# 代表当前部门下所有员工列表，最优的写法只要在其中一个对象中关联就可以
	# 参数必须是另外一个相关联的类名
	# emps = relationship('Emp')

	def __repr__(self):
		return f'DEPT:<部门编号：{self.dept_no}，部门名称：{self.d_name}，部门城市：{self.city}>'


class Emp(Base):
	__tablename__ = 't_emp'
	emp_no = Column(Integer, primary_key = True)
	e_name = Column(String(50))
	job = Column(String(50))
	hire_date = Column(DATE)
	sal = Column(DECIMAL(10, 2))
	# 关注外键关联
	dept_no = Column(Integer, ForeignKey('t_dept.dept_no', ondelete = 'RESTRICT'))
	# dept_no = Column(Integer, ForeignKey('t_dept.dept_no', ondelete = 'CASCADE'))
	# lazy 的默认值是 select
	dept = relationship('Dept', backref = backref('emps', lazy = 'dynamic'), lazy = 'select')

	def __repr__(self):
		return f'EMP:<员工编号：{self.emp_no}，员工姓名： {self.e_name}，职位： {self.job}，入职日期： {self.hire_date}，工资： {self.sal}>'


# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)

# d1 = Dept(d_name = '行政部', city = '北京')
# e1 = Emp(e_name = '张三', job = 'manager', hire_date = date(2020, 11, 22), sal = 5555.55, dept_no = 1)
# e2 = Emp(e_name = '李四', job = 'manager', hire_date = date(2020, 11, 22), sal = 5555.55, dept_no = 1)

session = sessionmaker(bind = engine)()

def test_lazy():
	d = session.query(Dept).filter(Dept.dept_no == 1)
	print(type(d))
	print(d)
	result = d.first()
	# 当前对象是一个 AppenderQuery 对象，可以追加，排序，过滤
	print(result.emps)
	e = Emp(e_name = 'xxx', job = 'xxx', hire_date = date(2020, 11, 22), sal = 2222)
	result.emps.append(e)
	print(result.emps.filter(Emp.emp_no > 1).all())
	session.commit()

def test_group():
	# 统计每个工资级别下有多少员工
	result = session.query(Emp.sal, count(Emp.emp_no)).group_by(Emp.sal).all()
	# 统计每个工资级别下有多少员工，统计薪资大于3000以上
	result = session.query(Emp.sal, count(Emp.emp_no)).group_by(Emp.sal).having(Emp.sal > 3000).all()
	print(result)


def test_subquery():
	# 查询和张三这个员工的职位，入职时间都相同的员工
	stmt = session.query(Emp.hire_date.label('hire_date'), Emp.job.label('job')).filter(Emp.e_name == '张三').subquery()
	result  = session.query(Emp).filter(Emp.hire_date == stmt.c.hire_date, Emp.job == stmt.c.job).all()
	print(result)


if __name__ == '__main__':
    test_subquery()
